/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package db;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import vanScheduling.Driver;
import vanScheduling.User;

/**
 *
 * @author Dan
 */
public class UserDAO extends DAOTest {

    public synchronized static boolean addUser(User user) {
        Connection con = getConnection();
        boolean result = false;

        try {
            CallableStatement cs = con.prepareCall("{call dbo.stp_insUser(?, ?, ?)}");
            cs.setString(1, user.getUsername());
            cs.setString(2, user.getPassword());
            cs.setInt(3, user.getPermissionLevelID());
            int added = cs.executeUpdate();

            result = added > 0;

            cs.close();
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return result;
    }

    public static List<User> getUsers() {
        Connection con = getConnection();
        ArrayList<User> users = new ArrayList<User>();
        try {
            CallableStatement cs = con.prepareCall("{call stp_selAllUsers}");
            ResultSet rs = cs.executeQuery();

            while (rs.next()) {
                int id = rs.getInt("ID");
                String username = rs.getString("Username");
                String password = rs.getString("Password");
                int permissionLevelID = rs.getInt("PermissionLevelID");
                int driverID = rs.getInt("DriverID");
                String permissionLevelDesc = rs.getString("PermissionLevelDesc");
                String driverName = rs.getString("DriverName");
                users.add(new User(id, username, password, permissionLevelID, driverID, permissionLevelDesc, driverName));
            }

            cs.close();
            con.close();
        } catch (SQLException e) {
            System.err.println(e.getMessage());
            e.printStackTrace();
        }
        return users;
    }

    public synchronized static boolean deleteUser(User user) {
        Connection con = getConnection();
        boolean result = false;
        try {
            CallableStatement cs = con.prepareCall("{call stp_delUser(?)}");
            cs.setInt(1, user.getId());
            int delete = cs.executeUpdate();

            result = delete > 0;
        } catch (SQLException e) {
            System.err.println(e.getMessage());
            e.printStackTrace();
        }
        return result;
    }

//    public static User getUserByID(int userID) {
//        Connection con = getConnection();
//        boolean result = false;
//        User returnUser = null;
//
//        try {
//            CallableStatement cs = con.prepareCall("{call dbo.stp_selUserByID(?)}");
//            cs.setInt(1, userID);
//
//            ResultSet rs = cs.executeQuery();
//
//            while (rs.next())
//            {
//                int id = rs.getInt("ID");
//                String username = rs.getString("Username");
//                String password = rs.getString("Password");
//                int permissionLevelID = rs.getInt("PermissionLevelID");
//                int driverID = rs.getInt("DriverID");
//                String permissionLevelDesc = rs.getString("PermissionLevelDesc");
//                String driverName = rs.getString("DriverName");
//                returnUser = new User(id, username, password, permissionLevelID, driverID, permissionLevelDesc, driverName);
//            }
//
//            cs.close();
//            con.close();
//        }
//        catch (SQLException e) {e.printStackTrace();}
//        return returnUser;
//    }
    public synchronized static boolean saveUser(User user) {
        Connection con = getConnection();
        boolean result = false;

        try {
            CallableStatement cs = con.prepareCall("{call dbo.stp_updUser(?, ?, ?, ?)}");
            cs.setInt(1, user.getId());
            cs.setString(2, user.getPassword());
            cs.setInt(3, user.getPermissionLevelID());
            cs.setInt(4, user.getDriverID());
            int added = cs.executeUpdate();

            result = added > 0;

            cs.close();
            con.close();
        } catch (SQLException e) {
            System.err.println(e.getMessage());
            e.printStackTrace();
        }

        return result;
    }

    public synchronized static boolean addUserDriverID(Driver driver)
    {
        User logged = User.getLoggedInUser();
        logged.setDriverID(driver.getId());
        
        return saveUser(logged);
    }

    public static User getUserByUserName(String userName) {
        Connection con = getConnection();
        boolean result = false;
        User returnUser = null;

        try {
            CallableStatement cs = con.prepareCall("{call dbo.stp_selUserByUserName(?)}");
            cs.setString(1, userName);

            ResultSet rs = cs.executeQuery();

            while (rs.next()) {
                int id = rs.getInt("ID");
                String username = rs.getString("Username");
                String password = rs.getString("Password");
                int permissionLevelID = rs.getInt("PermissionLevelID");
                int driverID = rs.getInt("DriverID");
                String permissionLevelDesc = rs.getString("PermissionLevelDesc");
                String driverName = rs.getString("DriverName");
                returnUser = new User(id, username, password, permissionLevelID, driverID, permissionLevelDesc, driverName);
            }

            cs.close();
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return returnUser;
    }
}
